package cn.big.bigd.biocode.dao;


import cn.big.bigd.biocode.model.Rate;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 *@Author wangfan
 *@Date 2018/1/18 10:30
 */


@Repository
public interface RateDAO extends JpaRepository<Rate, Long> {

    @Query(value = "SELECT stars from rates WHERE rater_id =?1 and rateable_id =?2 and dimension=?3",nativeQuery = true)
    Double calStarByUserAndToolAndDimension(int userId,int toolId,String dimension);

    @Query(value = "SELECT id from rates WHERE rater_id =?1 and rateable_id =?2 and dimension=?3",nativeQuery = true)
    Integer calIdByUserAndToolAndDimension(int userId,int toolId,String dimension);

    @Query(value = "SELECT DISTINCT (rater_id) from rates WHERE rateable_id =?1",nativeQuery = true)
    List<Integer> calUserIdsByTools(int toolId);

    @Query(value = "SELECT u.email as email,r.created_at as createAt from rates r,users u WHERE r.rater_id=u.id and r.rater_id =?1 AND r.rateable_id=?2 AND r.rateable_type=?3 ORDER BY  r.created_at ASC LIMIT 1 ;",nativeQuery = true)
    List<Object[]> calRateUserAndCreatedAt(int rId, int raId, String type);




    Rate findByRaterIdAndRateableTypeAndRateableIdAndDimension(int rId,String type,int raId,String dimension);


    @Query(value = "SELECT ifnull(AVG(stars),0) FROM rates WHERE rateable_id =?1 AND dimension=?2 AND rateable_type=?3",nativeQuery = true)
    double calAvgStarByRateableAndDimensionAndType(int rateableId,String dimension,String type);


    @Query(value = "SELECT SUM(stars) FROM rates WHERE rateable_id =?1 AND dimension=?2 AND rateable_type=?3",nativeQuery = true)
    double calSumStarByRateableAndDimensionAndType(int rateableId,String dimension,String type);

    @Query(value = "SELECT count(DISTINCT rater_id) FROM rates WHERE rateable_id=?1 AND rateable_type=?2",nativeQuery = true)
    int countDistinctRateIdByRateableIdAndRateableType(int rateableId,String type);



}
